package com.iterative.mysql.util;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import org.hibernate.engine.spi.SessionFactoryImplementor;

import org.json.simple.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;

import com.iterative.common.util.ApplicationContextUtil;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;


/**
 * 数据库操作语言
 * @author like
 *
 */
public class MysqlDML {
    private static SessionFactory sessionFactory = (SessionFactory) ApplicationContextUtil.getContext().getBean("sessionFactory");

    public static String getSelectTableNameBySql(String sql) {
        String tableName = null;

        if (sql.contains("from")) {
            String[] sqlKey = sql.split("from");
            sqlKey = sqlKey[1].trim().split(" ");
            tableName = sqlKey[0];
        }

        return tableName;
    }

    @SuppressWarnings("unchecked")
    public static List<JSONObject> getSelectResult(String sql) {
        List<JSONObject> respList = new ArrayList<JSONObject>();
        String tableName = getSelectTableNameBySql(sql);

        try {
            PreparedStatement pst = getCon().prepareStatement(sql);
            ResultSet ret = null;
            ret = pst.executeQuery();
            
            while (ret.next()) {
                JSONObject respJson = new JSONObject();
                DatabaseMetaData dbMetData = getCon().getMetaData();

                if (sql.split("from")[0].contains("*")) {
                    ResultSet colRet = dbMetData.getColumns(null, "%",
                            tableName, "%");
                    int i = 1;

                    while (colRet.next()) {
                        String columnName = colRet.getString("COLUMN_NAME");
                        respJson.put(columnName, ret.getString(i));
                        i++;
                    }
                } else {
                	String sqlC;
                	sqlC = sql.split("from")[0].trim().split("select")[1].trim();

                    String[] column = null;

                    if (sql.contains(",")) {
                        column = sqlC.split(",");
                        
                        for (int n = 0; n < column.length; n++) {
                        	if(column[n].contains("as")){
                        		respJson.put(column[n].split("as")[1].trim(), ret.getString(n + 1));
                        	}else if(column[n].contains("AS")){
                        		respJson.put(column[n].split("AS")[1].trim(), ret.getString(n + 1));
                        	}else{
                        		respJson.put(column[n], ret.getString(n + 1));
                        	}
                        }
                    } else {
                    	if(sqlC.contains("as")){
                    		respJson.put(sqlC.split("as")[1].trim(), ret.getString(1));
                    	}else if (sqlC.contains("AS")){
                    		respJson.put(sqlC.split("AS")[1].trim(), ret.getString(1));
                    	}else{
                    		respJson.put(sqlC, ret.getString(1));	
                    	}
                        
                    }
                }

                respList.add(respJson);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return respList;
    }

    private static Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    public List<Object> queryBySql(String sql) {
        @SuppressWarnings("unchecked")
        List<Object> list = getSession().createSQLQuery(sql).list();

        return list;
    }

    public int excuteBySql(String sql) {
        int result;
        SQLQuery query = this.getSession().createSQLQuery(sql);
        result = query.executeUpdate();

        return result;
    }
   private  static Connection connection = null;
    @SuppressWarnings("deprecation")
    private static Connection getCon() {
    	if(connection == null){
    		 try {
    	            connection =  ((SessionFactoryImplementor) getSession().getSessionFactory()).getConnectionProvider()
    	                    .getConnection();
    	        } catch (SQLException e) {
    	            e.printStackTrace();
    	        }
    	}
       

        return connection;
    }
}
